﻿using apitest.Application.Entities.Pms;
using apitest.Application.Test.Models;
using MiniExcelLibs;
using Newtonsoft.Json;

namespace apitest.Application.Test.Services
{
    [DynamicApiController]
    public class Test3Service
    {
        public readonly ISqlSugarClient _db;
        public Test3Service(ISqlSugarClient db)
        {
            _db = db;
        }

        [HttpGet]
        public async Task<dynamic> T1()
        {
            var path = "C:\\Users\\Lenovo\\Desktop\\aaa.xlsx";
            var list = MiniExcel.Query(path, useHeaderRow: true, sheetName: "总表");
            var res = JsonConvert.DeserializeObject<List<Test3_class>>(JsonConvert.SerializeObject(list));
            return res.FirstOrDefault();
        }

        [HttpGet]
        public async Task<dynamic> T2()
        {
            var path = "C:\\Users\\Lenovo\\Desktop\\aaa.xlsx";
            var list = MiniExcel.Query(path, useHeaderRow: true, sheetName: "项目号对应CAT");
            return list;
            var res = JsonConvert.DeserializeObject<List<Test3_class>>(JsonConvert.SerializeObject(list));
            return res.FirstOrDefault();
        }

        [HttpGet]
        public async Task<dynamic> T3()
        {
            var dic = await _db.Queryable<T_Dict>()
                  .FirstAsync(o => o.Key == "ga_testcategory");
            var cateList = JsonConvert.DeserializeObject<List<category_class>>(dic.Value.ToString());
            var cates = cateList.Select(o => o.name).Distinct().ToList();
            var path = "C:\\Users\\Lenovo\\Desktop\\aaa.xlsx";
            var list = MiniExcel.Query(path, useHeaderRow: true, sheetName: "总表");
            var dataList = JsonConvert.DeserializeObject<List<Test3_class>>(JsonConvert.SerializeObject(list));
            var dataCates = dataList.Select(o => o.检测项目).Distinct().ToList();
            var res = new List<string>();
            foreach (var item in dataCates)
            {
                if (!cates.Contains(item))
                {
                    res.Add(item);
                }
            }
            return res;
        }

        [HttpGet]
        public async Task<dynamic> T4()
        {
            var path = "C:\\Users\\Lenovo\\Desktop\\bbb.xlsx";
            var list = MiniExcel.Query(path, useHeaderRow: true, sheetName: "总表");
            var dataList = JsonConvert.DeserializeObject<List<Test3_class>>(JsonConvert.SerializeObject(list));
            var list1 = dataList.Select(o => o.送检人).Distinct().ToList();
            var list2 = dataList.Select(o => o.检测人员).Distinct().ToList();
            var userList = list1.Concat(list2).Distinct().ToList();
            var allUser = await _db.Queryable<User>()
                .Select(o => o.Name)
                .Distinct()
                .ToListAsync();
            var res = new List<string>();
            foreach (var item in userList)
            {
                if (!allUser.Contains(item))
                {
                    res.Add(item);
                }
            }
            return res;
        }

        [HttpGet]
        public async Task<dynamic> T5()
        {

            var dic = await _db.Queryable<T_Dict>()
                  .FirstAsync(o => o.Key == "ga_testtype");
            var cateList = JsonConvert.DeserializeObject<List<string>>(dic.Value.ToString());
            var path = "C:\\Users\\Lenovo\\Desktop\\bbb.xlsx";
            var list = MiniExcel.Query(path, useHeaderRow: true, sheetName: "总表");
            var dataList = JsonConvert.DeserializeObject<List<Test3_class>>(JsonConvert.SerializeObject(list));
            var dataCates = dataList.Select(o => o.样品类型).Distinct().ToList();
            var res = new List<string>();
            foreach (var item in dataCates)
            {
                if (!cateList.Contains(item))
                {
                    res.Add(item);
                }
            }
            return res;
        }



        // [HttpGet]
        // public async Task<dynamic> import()
        // {
        //     var allUser = await _db.Queryable<User>()
        //.ToListAsync();
        //     var path = "C:\\Users\\Lenovo\\Desktop\\bbb.xlsx";
        //     var projectCat = MiniExcel.Query(path, useHeaderRow: true, sheetName: "项目号对应CAT");
        //     var projectCatList = JsonConvert.DeserializeObject<List<Test3_class1>>(JsonConvert.SerializeObject(projectCat));
        //     var list = MiniExcel.Query(path, useHeaderRow: true, sheetName: "总表");
        //     var dataList = JsonConvert.DeserializeObject<List<Test3_class>>(JsonConvert.SerializeObject(list));
        //     var mainList = new List<GuangAn_TestRecord>();
        //     var detailList = new List<GuangAn_TestRecordDetail>();
        //     foreach (var item in dataList)
        //     {
        //         var cat = projectCatList.FirstOrDefault(o => o.项目编号 == item.项目编号)?.Cat;
        //         var sampleUserName = "";
        //         int? sampleUserId = 0;
        //         var testUserName = "";
        //         int? testUserId = 0;
        //         if (item.送检人 == "QC")
        //         {
        //             sampleUserName = item.检测人员;
        //         }
        //         else
        //         {
        //             sampleUserName = item.送检人;
        //         }
        //         sampleUserId = allUser.FirstOrDefault(o => o.Name == sampleUserName)?.Id;
        //         testUserName = item.检测人员;
        //         testUserId = allUser.FirstOrDefault(o => o.Name == testUserName)?.Id;
        //         var time = DateTime.Parse(item.接收时间);
        //         var main = new GuangAn_TestRecord()
        //         {
        //             Analysis = item.分析室,
        //             Batch = item.批号,
        //             Cat = cat,
        //             Categorys = JsonConvert.SerializeObject(new List<string>() { item.检测项目 }),
        //             CreateTime = time,
        //             CreateUserId = sampleUserId,
        //             CreateUserName = sampleUserName,
        //             FinishTime = time,
        //             StartTime = time,
        //             Department = item.送检部门,
        //             ProjectCode = item.项目编号,
        //             TestType = item.样品类型,
        //             SampleFrom = item.样品来源,
        //             SampleUserId = sampleUserId,
        //             SampleUserName = sampleUserName,
        //             Status = "已完成",
        //         };
        //         mainList.Add(main);
        //         var id = await _db.Insertable(main).ExecuteReturnIdentityAsync();
        //         var detail = new GuangAn_TestRecordDetail()
        //         {
        //             RecordId = id,
        //             StartTime = time,
        //             EndTime = time,
        //             Instrument = item.仪器编号,
        //             Category = item.检测项目,
        //             TestUserId = testUserId,
        //             TestUserName = testUserName,
        //             Status = "已完成",
        //             IsDelete = false,
        //             Needles = 1,
        //         };
        //         detailList.Add(detail);
        //         Thread.Sleep(10);
        //     }
        //     await _db.Insertable(detailList).ExecuteCommandAsync();
        //     return new
        //     {
        //         a = mainList.Take(10).ToList(),
        //         b = detailList.Take(10).ToList()
        //     };
        // }

        //public async Task<dynamic> del()
        //{
        //    var start = DateTime.Parse("2024-10-13");
        //    var end = DateTime.Parse("2024-10-31");
        //    var list= await _db.Queryable<GuangAn_TestRecord>()
        //        .Where(o => o.TestType == "纯化水检验" && SqlFunc.Between(o.StartTime, start, end))
        //        .ToListAsync();
        //    var dateList = new List<DateTime>() {
        //        DateTime.Parse("2024-10-15"),
        //        DateTime.Parse("2024-10-22"),
        //        DateTime.Parse("2024-10-29"),

        //    };
        //    var strList = new List<string>()
        //    {
        //        "1",
        //        "2",
        //        "3",
        //        "4",
        //        "5",
        //        "6",
        //        "7",
        //    };
        //    var res = new List<int>();
        //    foreach (var item in list)
        //    {
        //        var str = item.Batch.Split("-")[1];
        //        if (dateList.Contains(item.StartTime) && strList.Contains(str))
        //        {

        //        }
        //        else
        //        {
        //            res.Add(item.Id);
        //        }
        //    }
        //    await _db.Deleteable<GuangAn_TestRecord>()
        //        .Where(o => res.Contains(o.Id))
        //        .ExecuteCommandAsync();
        //    await _db.Deleteable<GuangAn_TestRecordDetail>()
        //        .Where(o => res.Contains(o.RecordId))
        //        .ExecuteCommandAsync();
        //    return res.Count;
        //}

        public async Task<dynamic> Export()
        {
            var list = await _db.Queryable<GuangAn_TestRecord>()
                  .Take(10)
                  .ToListAsync();

            var ms = new MemoryStream();
            MiniExcel.SaveAs(ms, list);
            ms.Seek(0, SeekOrigin.Begin);
            return new FileStreamResult(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                FileDownloadName = "1112.xlsx"
            };

        }

        [HttpGet]
        public async Task<dynamic> tttt()
        {
            var res = await _db.Queryable<GuangAn_TestRecordDetail>()
                .LeftJoin<GuangAn_TestRecord>((t1, t2) => t1.RecordId == t2.Id)
                .Where((t1, t2) => t1.Status == "已完成")
                .GroupBy((t1, t2) => new
                {
                    t2.TestType,
                    t1.Category,
                    t2.Cat
                })
                .Select((t1, t2) => new
                {
                    t2.TestType,
                    t2.Cat,
                    t1.Category,
                    sum = SqlFunc.AggregateSum(t1.Needles)
                })
                .MergeTable()
                .OrderByDescending(o => o.sum)
                .ToListAsync();
            return res;
        }

        [HttpGet]
        public async Task<dynamic> aaaaa()
        {
            var allList = await _db.Queryable<QC_Stability_Record>()
                .Where(o => o.Status == "进行中" && SqlFunc.DateDiff(DateType.Day, DateTime.Now, o.PlanTime) < 3)
                .Select(o => new
                {
                    o.PlanTime,
                    diff = SqlFunc.DateDiff(DateType.Day, DateTime.Now, o.PlanTime)
                })
                .OrderBy(o => o.diff)
                .ToListAsync();
            var res = new Dictionary<string, int>();
            res.Add("num", allList.Count(o => o.diff < 0));
            res.Add("num0", allList.Count(o => o.diff == 0));
            res.Add("num1", allList.Count(o => o.diff == 1));
            res.Add("num2", allList.Count(o => o.diff == 2));

            return res;
        }

        //public async Task<dynamic> abc()
        //{
        //    var hrUserList = await _db.Queryable<HR_User>().ToListAsync();
        //    var pmsUserList = await _db.Queryable<User>().ToListAsync();
        //    var allDepartmentList = await _db.Queryable<HR_Department>().ToListAsync();
        //    var list = await _db.Queryable<TestRequest>()
        //        .Where(o => SqlFunc.Between(o.CreateTime, "2025-01-01", "2025-03-08"))
        //      //  .Take(10)
        //        .ToListAsync();
        //    var errList = new Dictionary<int, string>();
        //    var userId = 0;
        //    var departName = "";
        //    var pmsUser = new User();
        //    var hrUser = new HR_User();
        //    var depart = new HR_Department();
        //    foreach (var item in list)
        //    {
        //        userId = item.SampleUserId.HasValue ? item.SampleUserId.Value : item.CreateUserId;
        //        pmsUser = pmsUserList.FirstOrDefault(o => o.Id == userId);
        //        if (pmsUser == null)
        //        {
        //            errList.Add(item.Id, "pmsUser为空");
        //        }
        //        else
        //        {
        //            if (string.IsNullOrWhiteSpace(pmsUser.EmpNo))
        //            {
        //                errList.Add(item.Id, "pmsUser工号为空");
        //            }
        //            else
        //            {
        //                hrUser = hrUserList.FirstOrDefault(o => o.number == pmsUser.EmpNo);
        //                if (hrUser == null)
        //                {
        //                    errList.Add(item.Id, "hrUser为空");
        //                }
        //                else
        //                {
        //                    depart = allDepartmentList.FirstOrDefault(o => o.id == hrUser.deptId);
        //                    if (depart == null)
        //                    {
        //                        errList.Add(item.Id, "hr部门为空");
        //                    }
        //                    else
        //                    {
        //                        departName = depart.name;
        //                        await _db.Updateable<TestRequest>()
        //                               .SetColumns(o => new TestRequest() { DepartmentName = departName })
        //                               .Where(o => o.Id == item.Id)
        //                               .ExecuteCommandAsync();
        //                    }
        //                }
        //            }
        //        }
        //        Thread.Sleep(100);
        //    }
        //    return errList;
        //}

        public class Test3_class
        {
            public string 仪器编号 { get; set; }
            public string 送检人 { get; set; }
            public string 送检部门 { get; set; }
            public string 样品类型 { get; set; }
            public string 项目编号 { get; set; }
            public string 批号 { get; set; }
            public string CAT { get; set; }
            public string 分析室 { get; set; }
            public string 检测项目 { get; set; }
            public string 接收时间 { get; set; }
            public string 检测人员 { get; set; }
            public string 样品来源 { get; set; }
            public string 工时 { get; set; }
        }

        public class Test3_class1
        {
            public string 项目编号 { get; set; }
            public string Cat { get; set; }
        }

        public class category_class
        {
            public string name { get; set; }
            public double price { get; set; }
            public int hour { get; set; }
            public string group { get; set; }
            public string des { get; set; }
        }
    }
}
